﻿//功能描述：运营管理数据访问层
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

using Hong.Common.Encryption;
using Hong.IRepository;
using Hong.Model.SysDomain.Model_ContractManagement;
using Hong.Model.SysDomain.Model_CustomerManagement;
using Hong.Model.SysDomain.Model_OperationManagement;
using Hong.Model.SysDomain.Model_SystemSettings;
using Hong.Repository.Data;

using Microsoft.Extensions.Configuration;

namespace Hong.Repository.Repository.Repository_OperationManagement
{
    /// <summary>
    /// 运营管理
    /// </summary>
    public class OperationRepository: IOperationRepository
    {

        #region 构造器注入
        public IConfiguration Configuration { get; }
        private readonly SecurityStatic _securityStatic;
        private readonly T_HongShi_3Context db;
        public OperationRepository(IConfiguration configuration,T_HongShi_3Context _db,SecurityStatic securityStatic)
        {
            Configuration = configuration;
            _securityStatic = securityStatic;
            db = _db;
        }
        #endregion


        #region 项目信息

        #region 项目立项添加
        public int Add(Project p)
            {
                try
                {
                    db.Projects.Add(p);
                    var i = db.SaveChanges();
                    return i;
                }
                catch (Exception)
                {
                    throw;
                }

            }
        #endregion

        #region 项目列表（分页显示、多条件查询）
        public Page ShowProject(int pageindex , int pagesize , string ProjectName = "", string ProjectConId = "")
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                    {
                        Page page = new Page();
                        var list = conn.Query<Project>("select a.*,c.contractName as CName,d.personNmae as DName,p.depName as PName from Project a  join ContractInfo c on a.projectConId=c.contractId join Person d on a.projectRId=d.personId join Department p on a.projectDeptID=p.depId").ToList();
                        if (!string.IsNullOrEmpty(ProjectName))
                        {
                            list = list.Where(a => a.ProjectName.Contains(ProjectName)).ToList();
                        }
                        if (!string.IsNullOrEmpty(ProjectConId))
                        {
                            list = list.Where(a => a.ProjectConId.Contains(ProjectConId)).ToList();
                        }
                        page.totalCount = list.Count();
                        list = list.Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                        page.Data = list;
                        return page;
                    }


                }
                catch (Exception)
                {
                    throw;
                }
            }
        #endregion

        #region 项目关联合同（显示、多条件查询）

        public Page GetConPro(int pageindex, int pagesize, string ProjectName = "", string ProjectConId = "")
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    Page page = new Page();
                    var list = conn.Query<Project>("select a.*,b.contractName as CName from Project a join ContractInfo b on a.projectConId=b.contractId").ToList();
                    if (!string.IsNullOrEmpty(ProjectName))
                    {
                        list = list.Where(a => a.ProjectName.Contains(ProjectName)).ToList();
                    }
                    if (!string.IsNullOrEmpty(ProjectConId))
                    {
                        list = list.Where(a => a.ProjectConId.Contains(ProjectConId)).ToList();
                    }
                    page.totalCount = list.Count();
                    list = list.Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                    page.Data = list;
                    return page;
                }


            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目信息反填
        public Project FindPro(string ProjectId)
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    conn.Open();
                    string sql = string.Format("select * from Project where   ProjectId=@ProjectId");
                    var re = conn.Query<Project>(sql, new { ProjectId = ProjectId }).FirstOrDefault();
                    return re;

                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目信息修改
        public int UptPro(Project p)
        {
            try
            {
                db.Entry(p).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return db.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 获取甲方联系人
        public List<Person> ShowReponsble()
        {
            using (SqlConnection conn=new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Person>("select * from Person").ToList();
                return list;
            }
        }
        #endregion

        #region 获取合同信息
        public List<ContractInfo> SelCon(string ContractName = "")
        {
            using (SqlConnection conn=new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<ContractInfo>("select * from ContractInfo").ToList();
                if (!string.IsNullOrEmpty(ContractName))
                {
                    list = list.Where(a => a.ContractName.Contains(ContractName)).ToList();
                }
                return list;
            }
        }
        #endregion

        #region 获取部门信息
        public List<Department> GetDept()
        {
            using (SqlConnection conn=new SqlConnection(_securityStatic.Securityinfo()))
            {
                var list = conn.Query<Department>("select * from Department").ToList();
                return list;
            }
        }
        #endregion

        #region 项目信息导出
        public List<Project> GetPro()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<Project>("select a.*,c.contractName as CName,d.personNmae as DName,p.depName as PName from Project a  join ContractInfo c on a.projectConId=c.contractId join Person d on a.projectRId=d.personId join Department p on a.projectDeptID=p.depId").ToList();
                    return list;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目关联合同导出
        public List<dynamic> GetProExcel()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<dynamic>("select a.*,b.contractName as CName from Project a join ContractInfo b on a.projectConId=b.contractId").ToList();
                    return list;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目关联合同导出
        public List<dynamic> GetConPro()
        {
            try
            {
                using (IDbConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<dynamic>("select a.*,b.contractName as CName from Project a join ContractInfo b on a.projectConId=b.contractId").ToList();
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region 项目关联合同添加
        public int AddCon(int pid, int cid)
        {
            try
            {
                var list = db.ProCons.Where(a => a.ProId == pid).ToList();
                if (list != null)
                {
                    db.ProCons.RemoveRange(list);
                    db.SaveChanges();
                }
                ProCon pc = new ProCon()
                {
                    ProId = pid,
                    ConId = cid
                };
                db.ProCons.Add(pc);
                return db.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion

        #region 项目进度列表（分页显示、多条件查询）
        public Page GetProgress(int pageindex, int pagesize, string ProjectName = "", string ProjectDeptId = "")
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    Page page = new Page();
                    var list = conn.Query<Project>("select a.*,b.DepName DName from Project a join Department b on a.ProjectDeptId=b.DepId").ToList();
                    if (!string.IsNullOrEmpty(ProjectName))
                    {
                        list = list.Where(a => a.ProjectName.Contains(ProjectName)).ToList();
                    }
                    if (!string.IsNullOrEmpty(ProjectDeptId))
                    {
                        list = list.Where(a => a.ProjectDeptId.Contains(ProjectDeptId)).ToList();
                    }
                    page.totalCount = list.Count();
                    list = list.Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();

                    page.Data = list;
                    return page;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目进度统计导出
        public List<dynamic> GetGrassExcel()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
                {
                    var list = conn.Query<dynamic>("select a.*,b.DepName DName from Project a join Department b on a.ProjectDeptId=b.DepId").ToList();
                    return list;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 项目统计
        public List<ProjectType> ShowCountProject(string DName)
        {
            string sql = "exec p_select @DName";
            List<ProjectType> list = null;
            using (SqlConnection conn = new SqlConnection(_securityStatic.Securityinfo()))
            {
                list = conn.Query<ProjectType>(sql, new { DName }).ToList();
            }
            return list;
        }
        #endregion


        #endregion
    }

}
